﻿using Magicodes.ExporterAndImporter.Excel;
using NP.BPMReportPlatform.BPMPlatform;
using NP.BPMReportPlatform.DBHelper;
using NP.BPMReportPlatform.Utils;
using Serilog;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Volo.Abp.Application.Dtos;
using NP.BPMReportPlatform.Entities.BPM_Business.CXYXTHNR;
using NP.BPMReportPlatform.Entities.BPM_Business;
using NP.BPMReportPlatform.Reports.CXYXTHNRA.Dtos;
using NP.BPMReportPlatform.Entities.BPM_Platform;
using NP.BPMReportPlatform.Utilities;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Linq;
using Magicodes.ExporterAndImporter.Core;
using System.Reflection;
using Microsoft.EntityFrameworkCore.Storage;
using Aspose.Words.Saving;

namespace NP.BPMReportPlatform.Reports.CXYXTHNRA
{
    public class CXYXTHNRAService : BPMReportPlatformAppService, ICXYXTHNRAService
    {
        private readonly SqlSugarClient _db;
        private readonly SqlSugarClient _dbPlatform;
        private readonly IBPMUtilsAppService _bPMUtilsApp;
        private readonly IBPMPlatformAppService _iBPMPlatformApp;
        private readonly SqlSugarClient _dbEngine;
        private readonly ILogger _logger;
        private readonly IExcelImporter _importer;

        public CXYXTHNRAService(
            ISqlSugarAppService sqlSugarAppService
            ,IBPMUtilsAppService bPMUtilsApp
            , IBPMPlatformAppService iBPMPlatformApp
            , ILogger logger, IExcelImporter importer)
        {
            _db = sqlSugarAppService.GetBusinessInstance();
            _dbPlatform = sqlSugarAppService.GetPlatformInstance();
            _dbEngine = sqlSugarAppService.GetEngineInstance();
            _bPMUtilsApp = bPMUtilsApp;
            _iBPMPlatformApp = iBPMPlatformApp;
            _logger = logger;
            _importer = importer;
        }

        public async Task<PagedResultDto<GetCXYXTHNROutput>> GetAllAsync(GetCXYXTHNRInput input)
        {
            RefAsync<int> totalCount = 0;

            var listQueryable = GetQueryable(input);
            var listViewDto = await listQueryable.ToPageListAsync(input.PageIndex, input.PageSize, totalCount);

            var userId = await _iBPMPlatformApp.GetUserAsync(CurrentUser.UserName);

            listViewDto.ForEach(x => x.Url = _bPMUtilsApp.GetProcessUrl(x.InstanceNumber, userId));

            //返回结果
            return new PagedResultDto<GetCXYXTHNROutput>()
            {
                TotalCount = totalCount,
                Items = listViewDto
            };
        }

        public async Task<IWorkbook> ExportAsync(GetCXYXTHNRInput input)
        {
            var listQueryable = GetQueryable(input);
            var listViewDto = await listQueryable.ToListAsync();

            var workbook = new XSSFWorkbook();
            workbook = await ExportExcelWorkBook(workbook, listViewDto).ConfigureAwait(false);
            workbook = await ExportExcelWorkBook2(workbook, listViewDto).ConfigureAwait(false);
            workbook = await ExportExcelWorkBook3(workbook, listViewDto).ConfigureAwait(false);

            return workbook;
        }

        private async Task<XSSFWorkbook> ExportExcelWorkBook(XSSFWorkbook workbook, List<GetCXYXTHNROutput> dataList)
        {
            var sheet = workbook.CreateSheet("全部数据");
            IRow row = sheet.CreateRow(0); // 添加表头
            IFont font1 = workbook.CreateFont();
            font1.Boldweight = (short)FontBoldWeight.Bold;
            ICellStyle style1 = workbook.CreateCellStyle();
            style1.SetFont(font1);
            style1.VerticalAlignment = VerticalAlignment.Center;
            style1.Alignment = HorizontalAlignment.Center;
            style1.BorderBottom = BorderStyle.Thin;
            style1.BorderLeft = BorderStyle.Thin;
            style1.BorderRight = BorderStyle.Thin;
            style1.BorderTop = BorderStyle.Thin;

            var headerNames1 = typeof(GetCXYXTHNROutput)
                .GetProperties()
                .Where(x => x.CustomAttributes.Any(y => y.AttributeType == typeof(ExporterHeaderAttribute)) &&
                !string.IsNullOrEmpty(x.GetCustomAttributes<ExporterHeaderAttribute>().First().DisplayName))
                .Select(p => p.GetCustomAttributes<ExporterHeaderAttribute>().First().DisplayName).ToList();

            for (var i = 0; i < headerNames1.Count; i++)
            {
                sheet.SetColumnWidth(i, 15 * 256);
                row.CreateCell(i).SetCellValue(headerNames1[i]);
                row.GetCell(i).CellStyle = style1;
            }
            var index = 1;
            foreach (var data in dataList)
            {
                IRow rowtemp = sheet.CreateRow(index);
                rowtemp.CreateCell(0).SetCellValue(data.InstanceNumber);
                rowtemp.CreateCell(1).SetCellValue(data.ProcessStartTimeStr);
                rowtemp.CreateCell(2).SetCellValue(data.ProcessStartUserName);
                rowtemp.CreateCell(3).SetCellValue(data.OrderStatus);
                rowtemp.CreateCell(4).SetCellValue(data.IsNeedCommitteeStr);
                rowtemp.CreateCell(5).SetCellValue(data.ApplicantWorkNumber);
                rowtemp.CreateCell(6).SetCellValue(data.ApplicantUserName);
                rowtemp.CreateCell(7).SetCellValue(data.ApplicantUserOrganization);
                rowtemp.CreateCell(8).SetCellValue(data.CompanyCode);
                rowtemp.CreateCell(9).SetCellValue(data.CurrentSituationStr);
                rowtemp.CreateCell(10).SetCellValue(data.CostCenter);
                rowtemp.CreateCell(11).SetCellValue(data.ValidStartTimeStr);
                rowtemp.CreateCell(12).SetCellValue(data.ValidEndTimeStr);
                rowtemp.CreateCell(13).SetCellValue(data.ActivityPurpose);
                rowtemp.CreateCell(14).SetCellValue(data.CommitteeArrangeContent);
                rowtemp.CreateCell(15).SetCellValue(data.ItemName);
                rowtemp.CreateCell(16).SetCellValue(data.CooperationSchool);
                rowtemp.CreateCell(17).SetCellValue(data.ContractCompany);
                rowtemp.CreateCell(18).SetCellValue(data.CooperationFunds?.ToString() ?? "");
                rowtemp.CreateCell(19).SetCellValue(data.CooperationStartTimeStr);
                rowtemp.CreateCell(20).SetCellValue(data.CooperationEndTimeStr);
                rowtemp.CreateCell(21).SetCellValue(data.ActivityDescription);
                rowtemp.CreateCell(22).SetCellValue(data.IsHighTechApplicationStr);
                rowtemp.CreateCell(23).SetCellValue(data.ImplementItemName);
                rowtemp.CreateCell(24).SetCellValue(data.ImplementCooperationSchool);
                rowtemp.CreateCell(25).SetCellValue(data.ImplementCooperationStartTimeStr);
                rowtemp.CreateCell(26).SetCellValue(data.ImplementCooperationEndTimeStr);
                rowtemp.CreateCell(27).SetCellValue(data.ImplementCooperationFunds?.ToString() ?? "");
                index++;
            }

            return workbook;
        }

        private async Task<XSSFWorkbook> ExportExcelWorkBook2(XSSFWorkbook workbook, List<GetCXYXTHNROutput> dataList)
        {
            dataList = dataList.Where(x => x.IsNeedCommitteeStr == "是").ToList();
            var sheet = workbook.CreateSheet("产学研活动需求");
            IRow row = sheet.CreateRow(0); // 添加表头
            IFont font1 = workbook.CreateFont();
            font1.Boldweight = (short)FontBoldWeight.Bold;
            ICellStyle style1 = workbook.CreateCellStyle();
            style1.SetFont(font1);
            style1.VerticalAlignment = VerticalAlignment.Center;
            style1.Alignment = HorizontalAlignment.Center;
            style1.BorderBottom = BorderStyle.Thin;
            style1.BorderLeft = BorderStyle.Thin;
            style1.BorderRight = BorderStyle.Thin;
            style1.BorderTop = BorderStyle.Thin;

            var headerNames1 = typeof(ExportCXYXTHNRDto1)
                .GetProperties()
                .Where(x => x.CustomAttributes.Any(y => y.AttributeType == typeof(ExporterHeaderAttribute)))
                .Select(p => p.GetCustomAttributes<ExporterHeaderAttribute>().First().DisplayName).ToList();

            for (var i = 0; i < headerNames1.Count; i++)
            {
                sheet.SetColumnWidth(i, 15 * 256);
                row.CreateCell(i).SetCellValue(headerNames1[i]);
                row.GetCell(i).CellStyle = style1;
            }
            var index = 1;
            foreach (var data in dataList)
            {
                IRow rowtemp = sheet.CreateRow(index);
                rowtemp.CreateCell(0).SetCellValue(data.ProcessStartUserName);
                rowtemp.CreateCell(1).SetCellValue(data.ApplicantUserOrganization);
                rowtemp.CreateCell(2).SetCellValue(data.CompanyCode);
                rowtemp.CreateCell(3).SetCellValue(data.CurrentSituationStr);
                rowtemp.CreateCell(4).SetCellValue(data.CostCenter);
                rowtemp.CreateCell(5).SetCellValue(data.ValidStartTimeStr);
                rowtemp.CreateCell(6).SetCellValue(data.ValidEndTimeStr);
                rowtemp.CreateCell(7).SetCellValue(data.ActivityPurpose);
                rowtemp.CreateCell(8).SetCellValue(data.CommitteeArrangeContent);
                index++;
            }

            return workbook;
        }

        private async Task<XSSFWorkbook> ExportExcelWorkBook3(XSSFWorkbook workbook, List<GetCXYXTHNROutput> dataList)
        {
            dataList = dataList.Where(x => x.IsNeedCommitteeStr == "否").ToList();
            var sheet = workbook.CreateSheet("产学研活动计划");
            IRow row = sheet.CreateRow(0); // 添加表头
            IFont font1 = workbook.CreateFont();
            font1.Boldweight = (short)FontBoldWeight.Bold;
            ICellStyle style1 = workbook.CreateCellStyle();
            style1.SetFont(font1);
            style1.VerticalAlignment = VerticalAlignment.Center;
            style1.Alignment = HorizontalAlignment.Center;
            style1.BorderBottom = BorderStyle.Thin;
            style1.BorderLeft = BorderStyle.Thin;
            style1.BorderRight = BorderStyle.Thin;
            style1.BorderTop = BorderStyle.Thin;

            var headerNames1 = typeof(ExportCXYXTHNRDto2)
                .GetProperties()
                .Where(x => x.CustomAttributes.Any(y => y.AttributeType == typeof(ExporterHeaderAttribute)))
                .Select(p => p.GetCustomAttributes<ExporterHeaderAttribute>().First().DisplayName).ToList();

            for (var i = 0; i < headerNames1.Count; i++)
            {
                sheet.SetColumnWidth(i, 15 * 256);
                row.CreateCell(i).SetCellValue(headerNames1[i]);
                row.GetCell(i).CellStyle = style1;
            }
            var index = 1;
            foreach (var data in dataList)
            {
                IRow rowtemp = sheet.CreateRow(index);
                rowtemp.CreateCell(0).SetCellValue(data.ProcessStartUserName);
                rowtemp.CreateCell(1).SetCellValue(data.ApplicantUserOrganization);
                rowtemp.CreateCell(2).SetCellValue(data.CompanyCode);
                rowtemp.CreateCell(3).SetCellValue(data.ItemName);
                rowtemp.CreateCell(4).SetCellValue(data.CooperationStartTimeStr);
                rowtemp.CreateCell(5).SetCellValue(data.CooperationEndTimeStr);
                rowtemp.CreateCell(6).SetCellValue(data.CooperationFunds?.ToString() ?? "");
                rowtemp.CreateCell(7).SetCellValue(data.IsHighTechApplicationStr);
                rowtemp.CreateCell(8).SetCellValue(data.ActivityDescription);
                index++;
            }

            return workbook;
        }

        private ISugarQueryable<GetCXYXTHNROutput> GetQueryable(GetCXYXTHNRInput input)
        {
            var currentUserId = Guid.Empty;
            if (!input.IsQueryAll)
            {
                var currentUserDto = _dbPlatform.Queryable<Users>().Where(x => x.WorkNumber.Equals(CurrentUser.UserName)).First();
                if (currentUserDto != null)
                {
                    currentUserId = currentUserDto.UserId;
                }
            }
            var listQueryable = _db.Queryable<CXYXTHNRHeader, BusinessProcessesNew>(
                    (x, b) => new JoinQueryInfos(JoinType.Left, x.InstanceNumber == b.InstanceId))
                .WhereIF(!input.IsQueryAll, x => x.AddUserId.Equals(currentUserId))
                .WhereIF(!string.IsNullOrWhiteSpace(input.InstanceNumber), x => x.InstanceNumber == input.InstanceNumber)
                .WhereIF(!input.OrderStatus.IsNullOrEmpty(), (x, b) => SqlFunc.ContainsArray(input.OrderStatus.ToArray(), b.ProcessStatusCode))
                .WhereIF(!string.IsNullOrWhiteSpace(input.AddUserName), x => x.AddUserName.Contains(input.AddUserName))
                .WhereIF(input.StartCreationTime != null && input.EndCreationTime != null,
                    x => x.AddTime >= input.StartCreationTime && x.AddTime <= input.EndCreationTime)
                .WhereIF(input.IsNeedCommittee.HasValue, x=>x.IsNeedCommittee == input.IsNeedCommittee)
                .WhereIF(!string.IsNullOrWhiteSpace(input.ApplicantWorkNumber), x => x.ApplicantWorkNumber == input.ApplicantWorkNumber)
                .WhereIF(!string.IsNullOrWhiteSpace(input.ApplicantUserOrganization), x => x.ApplicantUserOrganization.Contains(input.ApplicantUserOrganization))
                .WhereIF(!string.IsNullOrWhiteSpace(input.CompanyCode), x => x.CompanyCode == input.CompanyCode)
                .Select((x, b) => new GetCXYXTHNROutput()
                {
                    InstanceNumber = x.InstanceNumber,
                    ProcessStartTime = x.AddTime,
                    ProcessStartTimeStr = x.AddTime.ToString(DateFormatHelper.DateTimeFormat),
                    ProcessStartUserName = x.AddUserName,
                    OrderStatus = b.ProcessStatusName,
                    IsNeedCommittee = x.IsNeedCommittee,
                    IsNeedCommitteeStr = x.IsNeedCommittee.HasValue && x.IsNeedCommittee.Value ? "是" : "否",
                    ApplicantWorkNumber = x.ApplicantWorkNumber,
                    ApplicantUserName = x.ApplicantUserName,
                    ApplicantUserOrganization = x.ApplicantUserOrganization,
                    CompanyCode = x.CompanyCode,
                    CurrentSituation = x.CurrentSituation,
                    CurrentSituationStr = x.CurrentSituation.HasValue && x.CurrentSituation.Value ? "有" : "无",
                    CostCenter = x.CostCenter,
                    ValidStartTime = x.ValidStartTime,
                    ValidStartTimeStr = x.ValidStartTime != null ? x.ValidStartTime.Value.ToString(DateFormatHelper.DateFormat) : "",
                    ValidEndTime = x.ValidEndTime,
                    ValidEndTimeStr = x.ValidEndTime != null ? x.ValidEndTime.Value.ToString(DateFormatHelper.DateFormat) : "",
                    ActivityPurposeType = x.ActivityPurposeType,
                    ActivityPurpose = x.ActivityPurpose,
                    CommitteeArrangeContentType = x.CommitteeArrangeContentType,
                    CommitteeArrangeContent = x.CommitteeArrangeContent,
                    ItemName = x.ItemName,
                    CooperationSchool = x.CooperationSchool,
                    ContractCompany = x.ContractCompany,
                    CooperationFunds = x.CooperationFunds,
                    CooperationStartTime = x.CooperationStartTime,
                    CooperationStartTimeStr = x.CooperationStartTime != null ? x.CooperationStartTime.Value.ToString(DateFormatHelper.DateFormat) : "",
                    CooperationEndTime = x.CooperationEndTime,
                    CooperationEndTimeStr = x.CooperationEndTime != null ? x.CooperationEndTime.Value.ToString(DateFormatHelper.DateFormat) : "",
                    ActivityDescription = x.ActivityDescription,
                    IsHighTechApplication = x.IsHighTechApplication,
                    IsHighTechApplicationStr = x.IsHighTechApplication.HasValue && x.IsHighTechApplication.Value ? "是" : "否",
                    ImplementItemName = x.ImplementItemName,
                    ImplementCooperationSchool = x.ImplementCooperationSchool,
                    ImplementCooperationStartTime = x.ImplementCooperationStartTime,
                    ImplementCooperationStartTimeStr = x.ImplementCooperationStartTime != null ? x.ImplementCooperationStartTime.Value.ToString(DateFormatHelper.DateFormat) : "",
                    ImplementCooperationEndTime = x.ImplementCooperationEndTime,
                    ImplementCooperationEndTimeStr = x.ImplementCooperationEndTime != null ? x.ImplementCooperationEndTime.Value.ToString(DateFormatHelper.DateFormat) : "",
                    ImplementCooperationFunds = x.ImplementCooperationFunds,
                }).MergeTable().Distinct().OrderBy(ins => ins.ProcessStartTime, OrderByType.Desc);
            return listQueryable;
        }
    }
}
